package com.jwb.pub.serviceImpl;

import java.io.File;
import java.io.FileInputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.jwb.pub.dao.BaseDAO;
import com.jwb.pub.model.JavaModel;
import com.jwb.pub.model.JavaSql;
import com.jwb.pub.model.Page;
import com.jwb.pub.service.importService;
import com.sun.org.apache.bcel.internal.generic.FMUL;

@Service
public class ImportServiceImpl implements importService {
	
	@Autowired
	private BaseDAO baseDAO;
	
	@Override
	public JSONObject list(JSONObject jsdata, Page page) {
		JSONObject jsobject = new JSONObject();
		try{
			String sql = "SELECT import_id, oper_code, (SELECT real_name FROM user_info u WHERE u.user_id = oper_code) oper_name, "
					 + "DATE_FORMAT(import_date,'%Y-%m-%d %H:%i:%s') import_date, import_status,  file_name, file_url, " 
					 + "(SELECT d.code_desc FROM user_dict d WHERE d.dict_name = 'import_status' AND d.code = import_status ) import_status_name, "
					 + "DATE_FORMAT(status_date,'%Y-%m-%d %H:%i:%s') status_date FROM import_main WHERE 1=1 ";
			String sqlWhere = " ";
			if(jsdata.containsKey("tableId")){
				sqlWhere +=" AND table_id = "+jsdata.getString("tableId");
			}
			if(jsdata.containsKey("status")&&!"-1".equals(jsdata.getString("status"))){
				sqlWhere +=" AND import_status = '"+jsdata.getString("status")+"' ";
			}else if("-1".equals(jsdata.getString("status"))){
				sqlWhere +=" AND import_status != 'import_status_5' ";
			}
			
			if(jsdata.containsKey("startDate")&&!StringUtils.isEmpty(jsdata.getString("startDate"))){
				sqlWhere += " and STR_TO_DATE(DATE_FORMAT(import_date,'%Y-%m-%d'),'%Y-%m-%d') >= "
						+ "STR_TO_DATE('" + jsdata.getString("startDate") + " ','%Y-%m-%d')";
			}
			if(jsdata.containsKey("endDate")&&!StringUtils.isEmpty(jsdata.getString("endDate"))){
				sqlWhere +=" and STR_TO_DATE(DATE_FORMAT(import_date,'%Y-%m-%d'),'%Y-%m-%d') <= "
						+ "STR_TO_DATE('" + jsdata.getString("endDate") + " ','%Y-%m-%d')";
			}
			String sqlOrder = " ORDER BY import_date desc, import_id ";
			sql += sqlWhere+sqlOrder;
			jsobject = baseDAO.findBySqlToJosn(sql, page);			
		}catch(Exception e){
			e.printStackTrace();
			jsobject.put("respCode", "-1");
			jsobject.put("respDesc", "获取导入列表失败");
		}		
		return jsobject;
	}

	@Override
	public JSONObject tableInfo(JSONObject jsdata) {
		JSONObject jsobject = new JSONObject();
		try{
			
			String sql = "SELECT table_id, table_name, table_desc, template_url "
						 + "FROM import_table WHERE ";
			String sqlWhere = " 1=1 ";
			if(jsdata.containsKey("tableName")){
				sqlWhere += " AND STATUS = 'A' ";
				sqlWhere +="  AND table_name like '%"+jsdata.getString("tableName")+"%' ";
			}else{
				sqlWhere +=" AND table_id = '"+jsdata.getString("tableId")+"' ";
			}
			String sqlOrder = " ORDER BY table_id ";
			sql +=sqlWhere+sqlOrder;
			jsobject = baseDAO.findBySqlToJosn(sql);	
			
	        JSONArray jsarry = jsobject.getJSONArray("list");		  
	        for(int i=0;i<jsarry.size();i++){
	        	 JSONObject jso2 = jsarry.getJSONObject(i);
		       	 String sqlWhere2="";
	      		 String sqlOrder2="";
	      		 
				 String sql2="SELECT REPLACE(filed_name, 'f', ''), filed_name, filed_desc FROM import_table_detail  	WHERE table_id = 1 AND STATUS = 'A' ORDER BY CAST(REPLACE(filed_name, 'f', '') AS SIGNED), id	";
				 sql2 = "SELECT filed_name, filed_desc ,data_type,real_filed_name FROM import_table_detail "
							+"WHERE   STATUS = 'A' ";
			     if (null != jsarry.getJSONObject(i).getString("table_id") && !"".equals(jsarry.getJSONObject(i).getString("table_id") ))
				 {
					sqlWhere2 += " and table_id = "+jsarry.getJSONObject(i).getString("table_id");
		
				 }   
			   
			    sqlOrder2=" ORDER BY filed_name+0, id ";
			    sql2+=sqlWhere2+sqlOrder2;	
			    JSONObject jsobject2 =baseDAO.findBySqlToJosn(sql2);
			    
			    jso2.put("detaillist", jsobject2.getJSONArray("list"));
			    
			    
			    
	        }
			
			
			
		}catch(Exception e){
			e.printStackTrace();
			jsobject.put("respCode", "-1");
			jsobject.put("respDesc", "table数据获取异常");
		}		
		return jsobject;
	}
	
	@Override
	public JSONObject saveUpload(JSONObject jsdata){
		JSONObject jsobject = new JSONObject();
		String tableId = jsdata.getString("tableId");
		String root = jsdata.getString("root");
		String path = jsdata.getString("path");
		String filepath = jsdata.getString("filepath");
		String filename = jsdata.getString("filename");
		String opercode = jsdata.getString("oper_code");
		String import_status = jsdata.getString("import_status");
		String import_check_status = jsdata.getString("import_check_status");
		String importId = "";
		Workbook wb = null;
		try{
			File file = new File(filepath);
			if(!file.exists()){
				jsobject.put("respCode", "-1");
				jsobject.put("respDesc", "读取文件异常");
				return jsobject;
			}else if(path.endsWith(".xls")){
				wb = new HSSFWorkbook(new FileInputStream(file));
			}else if(path.endsWith(".xlsx")){
				wb = new XSSFWorkbook(file);
			}else{
				file.delete();
				jsobject.put("respCode", "-1");
				jsobject.put("respDesc", "文件格式不正确");
				return jsobject;
			}
			
			String sql = "INSERT INTO import_main SET oper_code = ?, import_date=NOW(), import_status = ?, "
					+ "table_id = ?, file_name = ?,file_url = ?, status_date=NOW() ";
			ArrayList<JavaModel> params = new ArrayList<>();
			params.add(new JavaModel(opercode));
			params.add(new JavaModel(import_status));
			params.add(new JavaModel(tableId));
			params.add(new JavaModel(filename));
			params.add(new JavaModel(path));
			baseDAO.excuteSql(sql, params);	
			
			sql= "SELECT import_id,file_url FROM import_main WHERE file_url='"+path+"' AND table_id ="+tableId;
			importId = baseDAO.findBySqlToJosn(sql).getJSONArray("list").getJSONObject(0).getInteger("import_id")+"";
			
			Sheet sheet = wb.getSheetAt(0);
			int firstRowIndex = sheet.getFirstRowNum()+1;
			int lastRowIndex = sheet.getLastRowNum();
			JSONArray list = new JSONArray();
			for(int row = firstRowIndex; row < lastRowIndex; row++) {  
				Row rinfo = sheet.getRow(row);
				if(rinfo!=null){
					JSONObject item = new JSONObject();
					int firstCellIndex = rinfo.getFirstCellNum();
                    int lastCellIndex = rinfo.getLastCellNum();
                    int idx = 1;
                    for (int col = firstCellIndex; col < lastCellIndex; col++) {  
                        Cell cell = rinfo.getCell(col);
                        item.put("f"+idx, getCellValue(cell));
                        idx++;
                    }		
                    item.put("import_id", importId);
                    item.put("oper_code", opercode);
                    item.put("import_check_status", import_check_status);
//                    item.put("reason", "");
					list.add(item);
				}				
			}
			
			
			int size = list.size();
			int num = size/10;
			if(size>0){
				LinkedHashMap<JavaSql,ArrayList<JavaModel>> map = new LinkedHashMap<>();
				for(int i=0;i<size;i++){
					params = new ArrayList<JavaModel>();
					sql = "INSERT INTO import_data_tmp SET oper_date=NOW() ";
					JSONObject jso = list.getJSONObject(i);
					for(String key:jso.keySet()){
						sql += ", "+key+"= ? ";
						if("import_id".equals(key)){
							params.add(new JavaModel(Integer.valueOf(jso.getString(key))));
						}else{
							params.add(new JavaModel(jso.getString(key)));
						}						
					}
					JavaSql jsql = new JavaSql(i, sql);
					map.put(jsql, params);
					
					if(i!=0&&(i+1)%10==0&&(i+1)/10<num){
						baseDAO.excuteBatchOrderedSqlEx(map);
						map.clear();
					}else if((i+1)/10==num&&i==size-1){
						baseDAO.excuteBatchOrderedSqlEx(map);
						map.clear();
					}				
				}
			}
			
				
			jsobject.put("respCode", "0");
			jsobject.put("respDesc", "上传成功");
		}catch(Exception e){
			e.printStackTrace();
			if(!StringUtils.isEmpty(importId)){
				jsdata = new JSONObject();
				jsdata.put("import_id", importId);
				jsdata.put("root", root);
				jsdata.put("file_url", path);
				jsdata.put("import_status", import_status);
				del(jsdata);
			}else{
				File file = new File(filepath);
				if(file.exists()) file.delete();
			}			
			jsobject.put("respCode", "-1");
			jsobject.put("respDesc", "上传文件数据异常");
		}finally{
			if(wb!=null) {
				try{
					wb.close();
				}catch(Exception e){
					e.printStackTrace();
				}
			}
		}
		return jsobject;
	}
	
	
	
	public JSONObject saveUploadEx(JSONObject jsdata) {
		JSONObject jsobject = new JSONObject();
		String tableId = jsdata.getString("tableId");
		String path = jsdata.getString("path");
		String filepath = jsdata.getString("filepath");
		String filename = jsdata.getString("filename");
		String opercode = jsdata.getString("oper_code");
		String import_status = jsdata.getString("import_status");
		
		String sql = "SELECT filed_name, filed_desc FROM import_table_detail "
				+"WHERE table_id = "+tableId+" AND STATUS = 'A' ORDER BY filed_name+0, id ";
		Workbook wb = null;
		try{
			JSONObject info = baseDAO.findBySqlToJosn(sql);
			if("0".equals(info.getString("respCode"))&&info.getJSONArray("list").size()>0){
				JSONArray cells = info.getJSONArray("list");
				File excel = new File(filepath);
				if(!excel.exists()){
					jsobject.put("respCode", "-1");
					jsobject.put("respDesc", "上传文件异常");
					return jsobject;
				}else if(filepath.endsWith(".xls")){
					wb = new HSSFWorkbook(new FileInputStream(excel));
				}else if(filepath.endsWith(".xlsx")){
					wb = new XSSFWorkbook(excel);
				}else{
					excel.delete();
					jsobject.put("respCode", "-1");
					jsobject.put("respDesc", "上传文件格式不正确");
					excel.delete();
					return jsobject;
				}
				
				Sheet sheet = wb.getSheetAt(0);
				int firstRowIndex = sheet.getFirstRowNum();
				int lastRowIndex = sheet.getLastRowNum();
				boolean valid = true;
				for(int row = firstRowIndex; row < lastRowIndex; row++) {  
					Row rinfo = sheet.getRow(row);
					if(rinfo!=null){
						int firstCellIndex = rinfo.getFirstCellNum();
	                    int lastCellIndex = rinfo.getLastCellNum();
	                    int idx = 0;
	                    for (int col = firstCellIndex; col < lastCellIndex; col++) {   //遍历列
	                        JSONObject cinfo = cells.getJSONObject(idx);
	                    	Cell cell = rinfo.getCell(col);
	                        if(StringUtils.isEmpty(cell)||!cell.toString().equalsIgnoreCase(cinfo.getString("filed_desc"))){
	                        	valid = false;
	                        	break;
	                        } 
	                        idx++;
	                    }					
						break;
					}			
				}
				
				if(valid){
					import_status = "import_check_status_2";
					jsobject.put("respCode", "0");
					jsobject.put("respDesc", "上传并验证数据成功");
				}else{
					import_status = "import_check_status_3";
					jsobject.put("respCode", "-2");
					jsobject.put("respDesc", "验证数据异常");
				}				
				
				
			}else{
				jsobject.put("respCode", "-9");
				jsobject.put("respDesc", "获取表头数据失败");
			}
			
						
		}catch(Exception e){
			e.printStackTrace();
			jsobject.put("respCode", "-1");
			jsobject.put("respDesc", "验证文件数据异常");
		}finally{
			if(wb!=null) {
				try{
					wb.close();
				}catch(Exception e){
					e.printStackTrace();
				}
			}
			sql = "INSERT INTO import_main SET oper_code = ?, import_date=NOW(), import_status = ?, "
					+ "table_id = ?, file_name = ?,file_url = ?, status_date=NOW() ";
			ArrayList<JavaModel> params = new ArrayList<>();
			params.add(new JavaModel(opercode));
			params.add(new JavaModel(import_status));
			params.add(new JavaModel(tableId));
			params.add(new JavaModel(filename));
			params.add(new JavaModel(path));
			baseDAO.excuteSql(sql, params);		
		}
		return jsobject;
	}
	
	
	public JSONObject del(JSONObject jsdata){
		JSONObject jsobject = new JSONObject();
		String importId = jsdata.getString("import_id");
		String root = jsdata.getString("root");
		String fileUrl = jsdata.getString("file_url");
		String import_status = jsdata.getString("import_status");
		try{
			Map<String,ArrayList<JavaModel>> map = new HashMap<>();
			String sql = "UPDATE import_main SET import_status='import_status_5' WHERE import_id = ? ";
			ArrayList<JavaModel> list = new ArrayList<>();
			list.add(new JavaModel(Integer.valueOf(importId)));
			map.put(sql, list);
			sql = "DELETE FROM import_data_tmp WHERE import_id = ? ";
			map.put(sql, list);			
			sql = "UPDATE attach_file_detail SET status = 'P' WHERE file_path = ? ";
			list = new ArrayList<>();
			list.add(new JavaModel(fileUrl));
			map.put(sql, list);
			baseDAO.excuteBatchSql(map);
			
			File file = new File(root+fileUrl);
			if(file.exists()){
				file.delete();
			}
			jsobject.put("respCode", "0");
			jsobject.put("respDesc", "删除文件成功");
		}catch(Exception e){
			e.printStackTrace();
			jsobject.put("respCode", "-1");
			jsobject.put("respDesc", "删除文件失败");
		}	
		return jsobject;
	}
	
	public JSONObject reload(JSONObject jsdata){
		JSONObject jsobject = new JSONObject();
		String root = jsdata.getString("root");
		String importId = jsdata.getString("importId");
		String fileUrl = jsdata.getString("fileUrl");
		String tableId = jsdata.getString("tableId");
		String path = jsdata.getString("path");
		String filepath = jsdata.getString("filepath");
		String filename = jsdata.getString("filename");
		String opercode = jsdata.getString("oper_code");
		String import_status = jsdata.getString("import_status");
		String import_check_status = jsdata.getString("import_check_status");
		Workbook wb = null;
		try{
			File file = new File(filepath);
			if(!file.exists()){
				jsobject.put("respCode", "-1");
				jsobject.put("respDesc", "读取文件异常");
				return jsobject;
			}else if(path.endsWith(".xls")){
				wb = new HSSFWorkbook(new FileInputStream(file));
			}else if(path.endsWith(".xlsx")){
				wb = new XSSFWorkbook(file);
			}else{
				file.delete();
				jsobject.put("respCode", "-1");
				jsobject.put("respDesc", "文件格式不正确");
				return jsobject;
			}
			
			Map<String,ArrayList<JavaModel>> map = new HashMap<>();
			String sql = "UPDATE import_main SET oper_code = ?, import_date=NOW(), import_status = ?, "
					+ " file_name = ?,file_url = ?, status_date=NOW() WHERE import_id = ? ";
			ArrayList<JavaModel> params = new ArrayList<>();
			params.add(new JavaModel(opercode));
			params.add(new JavaModel(import_status));
			params.add(new JavaModel(filename));
			params.add(new JavaModel(path));
			params.add(new JavaModel(Integer.valueOf(importId)));
			map.put(sql, params);
			sql = "DELETE FROM import_data_tmp WHERE import_id= ? ";
			params = new ArrayList<>();
			params.add(new JavaModel(importId));
			map.put(sql, params);
			sql = "UPDATE attach_file_detail SET status = 'P' WHERE file_path = ? ";
			params = new ArrayList<>();
			params.add(new JavaModel(fileUrl));
			map.put(sql, params);
			if(baseDAO.excuteBatchSql(map)){
				file = new File(root+fileUrl);
				if(file.exists()){
					file.delete();
				}				
			}			
			
			Sheet sheet = wb.getSheetAt(0);
			int firstRowIndex = sheet.getFirstRowNum()+1;
			int lastRowIndex = sheet.getLastRowNum();
			JSONArray list = new JSONArray();
			for(int row = firstRowIndex; row < lastRowIndex; row++) {  
				Row rinfo = sheet.getRow(row);
				if(rinfo!=null){
					JSONObject item = new JSONObject();
					int firstCellIndex = rinfo.getFirstCellNum();
                    int lastCellIndex = rinfo.getLastCellNum();
                    int idx = 1;
                    for (int col = firstCellIndex; col < lastCellIndex; col++) {  
                        Cell cell = rinfo.getCell(col);
                        item.put("f"+idx, getCellValue(cell));
                        idx++;
                    }		
                    item.put("import_id", importId);
                    item.put("oper_code", opercode);
                    item.put("import_check_status", import_check_status);
//                    item.put("reason", "");
					list.add(item);
				}				
			}
			
			int size = list.size();
			int num = size/10;
			if(size>0){
				LinkedHashMap<JavaSql,ArrayList<JavaModel>> lmap = new LinkedHashMap<>();
				for(int i=0;i<size;i++){
					params = new ArrayList<JavaModel>();
					sql = "INSERT INTO import_data_tmp SET oper_date=NOW() ";
					JSONObject jso = list.getJSONObject(i);
					for(String key:jso.keySet()){
						sql += ", "+key+"= ? ";
						if("import_id".equals(key)){
							params.add(new JavaModel(Integer.valueOf(jso.getString(key))));
						}else{
							params.add(new JavaModel(jso.getString(key)));
						}						
					}
					JavaSql jsql = new JavaSql(i, sql);
					lmap.put(jsql, params);
					
					if(i!=0&&(i+1)%10==0&&(i+1)/10<num){
						baseDAO.excuteBatchOrderedSqlEx(lmap);
						lmap.clear();
					}else if((i+1)/10==num&&i==size-1){
						baseDAO.excuteBatchOrderedSqlEx(lmap);
						lmap.clear();
					}				
				}
			}
			
			jsobject.put("respCode", "0");
			jsobject.put("respDesc", "上传数据成功");
		}catch(Exception e){
			e.printStackTrace();
			jsobject.put("respCode", "-1");
			jsobject.put("respDesc", "导入文件失败");
		}
		
		return jsobject;
	}
	

	public JSONObject reloadEx(JSONObject jsdata){
		JSONObject jsobject = new JSONObject();
		String root = jsdata.getString("root");
		String importId = jsdata.getString("importId");
		String fileUrl = jsdata.getString("fileUrl");
		String tableId = jsdata.getString("tableId");
		String path = jsdata.getString("path");
		String filepath = jsdata.getString("filepath");
		String filename = jsdata.getString("filename");
		String opercode = jsdata.getString("oper_code");
		String import_status = jsdata.getString("import_status");
		String sql = "SELECT filed_name, filed_desc FROM import_table_detail "
				+"WHERE table_id = "+tableId+" AND STATUS = 'A' ORDER BY filed_name+0, id ";
		Workbook wb = null;
		try{
			JSONObject info = baseDAO.findBySqlToJosn(sql);
			if("0".equals(info.getString("respCode"))&&info.getJSONArray("list").size()>0){
				JSONArray cells = info.getJSONArray("list");
				File excel = new File(filepath);
				if(!excel.exists()){
					jsobject.put("respCode", "-1");
					jsobject.put("respDesc", "上传文件异常");
					return jsobject;
				}else if(filepath.endsWith(".xls")){
					wb = new HSSFWorkbook(new FileInputStream(excel));
				}else if(filepath.endsWith(".xlsx")){
					wb = new XSSFWorkbook(excel);
				}else{
					excel.delete();
					jsobject.put("respCode", "-1");
					jsobject.put("respDesc", "上传文件格式不正确");
					excel.delete();
					return jsobject;
				}
				
				Sheet sheet = wb.getSheetAt(0);
				int firstRowIndex = sheet.getFirstRowNum();
				int lastRowIndex = sheet.getLastRowNum();
				boolean valid = true;
				for(int row = firstRowIndex; row < lastRowIndex; row++) {  
					Row rinfo = sheet.getRow(row);
					if(rinfo!=null){
						int firstCellIndex = rinfo.getFirstCellNum();
	                    int lastCellIndex = rinfo.getLastCellNum();
	                    int idx = 0;
	                    for (int col = firstCellIndex; col < lastCellIndex; col++) {   //遍历列
	                        JSONObject cinfo = cells.getJSONObject(idx);
	                    	Cell cell = rinfo.getCell(col);
	                        if(StringUtils.isEmpty(cell)||!cell.toString().equalsIgnoreCase(cinfo.getString("filed_desc"))){
	                        	valid = false;
	                        	break;
	                        } 
	                        idx++;
	                    }					
						break;
					}			
				}
				
				if(valid){
					import_status = "import_check_status_2";
					jsobject.put("respCode", "0");
					jsobject.put("respDesc", "上传并验证数据成功");
				}else{
					import_status = "import_check_status_3";
					jsobject.put("respCode", "-2");
					jsobject.put("respDesc", "验证数据异常");
				}				

				
			}else{
				jsobject.put("respCode", "-9");
				jsobject.put("respDesc", "获取表头数据失败");
			}
			
						
		}catch(Exception e){
			e.printStackTrace();
			jsobject.put("respCode", "-1");
			jsobject.put("respDesc", "验证文件数据异常");
		}finally{
			if(wb!=null) {
				try{
					wb.close();
				}catch(Exception e){
					e.printStackTrace();
				}
			}
			Map<String,ArrayList<JavaModel>> map = new HashMap<>();
			sql = "UPDATE import_main SET oper_code = ?, import_date=NOW(), import_status = ?, "
					+ " file_name = ?,file_url = ?, status_date=NOW() WHERE import_id = ? ";
			ArrayList<JavaModel> params = new ArrayList<>();
			params.add(new JavaModel(opercode));
			params.add(new JavaModel(import_status));
			params.add(new JavaModel(filename));
			params.add(new JavaModel(path));
			params.add(new JavaModel(Integer.valueOf(importId)));
			map.put(sql, params);
			sql = "UPDATE attach_file_detail SET status = 'P' WHERE file_path = ? ";
			params = new ArrayList<>();
			params.add(new JavaModel(fileUrl));
			map.put(sql, params);
			if(baseDAO.excuteBatchSql(map)){
				File file = new File(root+fileUrl);
				if(file.exists()){
					file.delete();
				}				
			}			
		}
		return jsobject;
	}
	
	@Override
	public JSONObject subImportData(JSONObject jsdata) {
		JSONObject jsobject = new JSONObject();
		jsobject.put("respCode", "-1");
		jsobject.put("respDesc", "迁移数据异常");		
		String tableId = jsdata.getString("tableId");
		String tableName = jsdata.getString("tableName");
		String importId = jsdata.getString("import_id");
		try{
			String sql = "SELECT filed_name, filed_desc, data_type, real_filed_name FROM import_table_detail"
					+ " WHERE status='A' AND table_id = "+tableId;
			JSONObject result = baseDAO.findBySqlToJosn(sql);
			if("0".equals(result.getString("respCode"))){
				sql = "INSERT INTO "+tableName;
				JSONArray list = result.getJSONArray("list");
				String fields = "";
				String real_fields ="";
//				INSERT app_tk_test (f1,...fn) SELECT col1...col2 FROM tablename
				for(int i=0;i<list.size();i++){
					JSONObject obj = list.getJSONObject(i);
					String field = obj.getString("filed_name");
					String real_field = obj.getString("real_filed_name");
					fields += field;
					fields += ",";
					real_fields += real_field;
					real_fields += ",";
				}
				fields = fields.substring(0, fields.length()-1);
				real_fields = real_fields.substring(0, real_fields.length()-1);
				Map<String,ArrayList<JavaModel>> map = new HashMap<>();
				sql += " ("+ real_fields+") SELECT "+fields+" FROM import_data_tmp WHERE import_id=? ";
				ArrayList<JavaModel> params = new ArrayList<>();
				params.add(new JavaModel(Integer.valueOf(importId)));
				map.put(sql, params);
				sql = "UPDATE import_main SET import_status = 'import_status_4' WHERE import_id=? ";
				map.put(sql, params);
				baseDAO.excuteBatchSql(map);				
				
				jsobject.put("respCode", "0");
				jsobject.put("respDesc", "迁移数据成功");	
			}			
			
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return jsobject;
	}
	
	
	public JSONObject subImportDataEx(JSONObject jsdata) {
		JSONObject jsobject = new JSONObject();
		jsobject.put("respCode", "-1");
		jsobject.put("respDesc", "迁移数据异常");		
		String tableId = jsdata.getString("tableId");
		String tableName = jsdata.getString("tableName");
		String importId = jsdata.getString("import_id");
		
		try{
			
			// 插入
			String sql = "SELECT filed_name, filed_desc, data_type, real_filed_name FROM import_table_detail"
					+ " WHERE status='A' AND table_id = "+tableId;
			JSONObject result = baseDAO.findBySqlToJosn(sql);
			if("0".equals(result.getString("respCode"))){
				JSONArray list = result.getJSONArray("list");
				String fields = "";
				JSONObject field_real = new JSONObject();
				JSONObject field_type = new JSONObject();
				for(int i=0;i<list.size();i++){
					JSONObject obj = list.getJSONObject(i);
					String field = obj.getString("filed_name");
					String real_field = obj.getString("real_filed_name");
					fields += field;
					fields += ",";
					field_real.put(field, real_field);
					field_type.put(real_field, obj.getString("data_type"));
				}
				fields = fields.substring(0, fields.length()-1);
				sql = " SELECT "+fields+" FROM import_data_tmp WHERE import_id="+importId;
				result = baseDAO.findBySqlToJosn(sql);
				if("0".equals(result.getString("respCode"))){
					// insert sql 
					// insert tablname set xxx = ?, xxx=?
					
					LinkedHashMap<JavaSql,ArrayList<JavaModel>> map = new LinkedHashMap<>();
					list = result.getJSONArray("list");
					int size = list.size();
					int num = size/10;
					
					for(int i=0;i<list.size();i++){
						sql = "INSERT INTO "+tableName+" SET ";
						JSONObject obj = list.getJSONObject(i);
						ArrayList<JavaModel> params = new ArrayList<>();
						for(String field:field_real.keySet()){
							String real  = field_real.getString(field);
							String value = obj.getString(field);
							sql += real+" = ?";
							sql += ",";
							params.add(format(value, field_type.getString(real)));
						}
						
						JavaSql jsql = new JavaSql(i, sql.substring(0, sql.length()-1));
						map.put(jsql, params);						
						if(i!=0&&(i+1)%10==0&&(i+1)/10<num){
							baseDAO.excuteBatchOrderedSqlEx(map);
							map.clear();
						}else if((i+1)/10==num&&i==size-1){
							baseDAO.excuteBatchOrderedSqlEx(map);
							map.clear();
						}	
						
					}
					
					
					 sql = "UPDATE import_main SET import_status = 'import_status_4' WHERE import_id=? ";
					 ArrayList<JavaModel> params = new ArrayList<>();
					 params.add(new JavaModel(Integer.valueOf(importId)));
					 baseDAO.excuteSql(sql, params);					 
				}
				
				jsobject.put("respCode", "0");
				jsobject.put("respDesc", "迁移数据成功");	
			}
						
			
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return jsobject;
	}
	
	private JavaModel format(String str,String fmt){
		JavaModel model = null;
		try{
			if("int".equalsIgnoreCase(fmt)){
				model = new JavaModel(Integer.valueOf(str));
			}else if("double".equals(fmt)){
				model = new JavaModel(Double.valueOf(str));
			}else if("datetime".equals(fmt)){
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				model = new JavaModel(sdf.parseObject(str));
			}else if("date".equals(fmt)){
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
				model = new JavaModel(sdf.parseObject(str));
			}else{
				model = new JavaModel(str);
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return model;
	}
	
	@Override
	public JSONObject check(JSONObject jsdata){
		JSONObject jsobject = new JSONObject();
		jsobject.put("respCode", "-1");
		jsobject.put("respDesc", "验证数据失败");
		String importId = jsdata.getString("import_id");
		String opercode = jsdata.getString("opercode");
		try{
			JSONObject result = tableInfo(jsdata);
			if("0".equals(result.getString("respCode"))){
				JSONObject info = result.getJSONArray("list").getJSONObject(0);
				JSONArray detail = info.getJSONArray("detaillist");
				String tablename = info.getString("table_name");
				int cnt = detail.size();
				String sql = "SELECT COUNT(*) FROM information_schema.columns WHERE table_name = '"+tablename+"' AND ( ";
				for(int i=0;i<detail.size();i++){
					JSONObject obj = detail.getJSONObject(i);
					if(i>0) sql +=" OR ";
					sql += " ( ";
					sql += " column_name = '"+obj.getString("real_filed_name")+"' ";
					sql += " AND data_type = '"+obj.getString("data_type")+"' ";
					sql += " )";
				}
				sql += ") ";
				if(cnt!=baseDAO.findBySqlCnt(sql)){
					jsobject.put("respCode", "-9");
					jsobject.put("respDesc", "表结构 【"+tablename+"】数据不匹配");
				}else{
					// 验证数据类型有效性
					sql = "SELECT * FROM import_data_tmp WHERE import_id = "+importId;
					result = baseDAO.findBySqlToJosn(sql);
					if("0".equals(result.getString("respCode"))){
						JSONArray list = result.getJSONArray("list");
						LinkedHashMap<JavaSql, ArrayList<JavaModel>> map = new LinkedHashMap<>();
						int size = list.size();
						int num = size/10;
						for(int i=0;i<size;i++){
							JSONObject item = list.getJSONObject(i);
							String import_check_status="import_check_status_2";
							for(int j=0;j<detail.size();j++){
								JSONObject obj = detail.getJSONObject(j);
								String type = obj.getString("data_type");
								String field = obj.getString("filed_name");
								boolean valid = chkDataType(type, item.getString(field));
								if(!valid){
									import_check_status = "import_check_status_3";
									break;
								}
							}	
							
							sql = "UPDATE import_data_tmp SET import_check_status=?, oper_code=?  WHERE import_id = ? AND id = ? ";
							JavaSql jsql = new JavaSql(i, sql);
							ArrayList<JavaModel> params = new ArrayList<>();
							params.add(new JavaModel(import_check_status));
							params.add(new JavaModel(opercode));
							params.add(new JavaModel(importId));
							params.add(new JavaModel(item.getInteger("id")));
							map.put(jsql, params);
							
							if(i!=0&&(i+1)%10==0&&(i+1)/10<num){
								baseDAO.excuteBatchOrderedSqlEx(map);
								map.clear();
							}else if((i+1)/10==num&&i==size-1){
								baseDAO.excuteBatchOrderedSqlEx(map);
								map.clear();
							}	
							
						}
						
						
						jsobject.put("respCode", "0");
						jsobject.put("respDesc", "验证数据完成!");
						
					}
					
					
				}				
				
				
			}
		}catch(Exception e){
			e.printStackTrace();
		}		
		return jsobject;
	}
	
	private boolean chkDataType(String type,String data){
		boolean valid = true;
		try{
			if(StringUtils.isEmpty(data)){
			}else if("int".equalsIgnoreCase(type)||"long".equalsIgnoreCase(type)){
				Pattern pattern = Pattern.compile("[0-9]*");
			    valid =  pattern.matcher(data).matches();			
			}else if("float".equalsIgnoreCase(type)||"double".equalsIgnoreCase(type)){
				valid = data.matches("[+-]?[0-9]+(\\.[0-9]+)?");
			}else if("date".equalsIgnoreCase(type)){
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
				sdf.parse(data);
			}else if("datetime".equalsIgnoreCase(type)){
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				sdf.parse(data);
			}
		}catch(Exception e){
			e.printStackTrace();
			valid = false;
		}
		return valid;
	}
	
	@Override
	public JSONObject detail(JSONObject jsdata,Page page){
		JSONObject jsobject = new JSONObject();
		jsobject.put("respCode", "-1");
		jsobject.put("respDesc", "获取数据异常");
		String importId = jsdata.getString("importId");
		try{
			JSONObject tableinfo = tableInfo(jsdata);
			if("0".equals(tableinfo.getString("respCode"))){
				JSONArray header = new JSONArray();
				JSONArray detaillist = tableinfo.getJSONArray("list").getJSONObject(0).getJSONArray("detaillist");
				//[{"data_type":"int","filed_desc":"序号\r\n","filed_name":"f1","real_filed_name":"id"},..]
				for(int i=0;i<detaillist.size();i++){
					JSONObject item = new JSONObject();
					JSONObject obj = detaillist.getJSONObject(i);
					item.put("col", obj.getString("filed_name"));
					item.put("desc", obj.getString("filed_desc"));
					header.add(item);
				}
				String sql = "SELECT * FROM import_data_tmp WHERE import_id ="+importId;
				jsobject = baseDAO.findBySqlToJosn(sql,page);
				jsobject.put("header", header);				
			}
			// {"list":[{"f1":"","f2":"",order:1}], header:[{"col":"f1","desc":"题目","order":0}], respCode:'', respDesc:''}
			
		}catch(Exception e){
			e.printStackTrace();			
		}
		return jsobject;
	}
	
	
	public JSONObject detailEx(JSONObject jsdata){
		JSONObject jsobject = new JSONObject();
		jsobject.put("respCode", "-1");
		jsobject.put("respDesc", "获取数据异常");
		String importId = jsdata.getString("importId");
		String root = jsdata.getString("root");
		Workbook wb = null;
		try{
			String sql = "SELECT file_url, file_name FROM import_main WHERE import_id ="+importId;
			JSONObject jso = baseDAO.findBySqlToJosn(sql);
			if("0".equals(jso.getString("respCode"))&&jso.getJSONArray("list").size()>0){
				String path = root+jso.getJSONArray("list").getJSONObject(0).getString("file_url");
				File file = new File(path);
				if(!file.exists()){
					jsobject.put("respCode", "-1");
					jsobject.put("respDesc", "读取文件异常");
					return jsobject;
				}else if(path.endsWith(".xls")){
					wb = new HSSFWorkbook(new FileInputStream(file));
				}else if(path.endsWith(".xlsx")){
					wb = new XSSFWorkbook(file);
				}else{
					jsobject.put("respCode", "-1");
					jsobject.put("respDesc", "文件格式不正确");
					return jsobject;
				}
				
				JSONArray header = new JSONArray();
				JSONArray list = new JSONArray();
				Sheet sheet = wb.getSheetAt(0);
				int firstRowIndex = sheet.getFirstRowNum();
				int lastRowIndex = sheet.getLastRowNum();
				int ridx = 1;
				for(int row = firstRowIndex; row < lastRowIndex; row++) {  
					Row rinfo = sheet.getRow(row);
					if(rinfo!=null){
						int firstCellIndex = rinfo.getFirstCellNum();
	                    int lastCellIndex = rinfo.getLastCellNum();
	                    int cidx = 1;
	                    JSONObject item = new JSONObject();
	                    for (int col = firstCellIndex; col < lastCellIndex; col++) {  
	                    	Cell cell = rinfo.getCell(col);
	                    	if(ridx==1){
	                    		JSONObject obj = new JSONObject();
	                    		obj.put("col", "f"+cidx);
	                    		obj.put("desc", cell.toString());
	                    		obj.put("order", cidx);
	                        	header.add(obj);
	                        }else{
	                        	item.put("f"+cidx, getCellValue(cell));
	                        }	                    	
	                        cidx++;
	                    }
	                    
	                    if(ridx>1){
		                    item.put("order", ridx);
		                    list.add(item);
	                    }
	                    ridx++;
					}			
				}
				
				jsobject.put("list", list);
				jsobject.put("header", header);
				jsobject.put("respCode", "0");
				jsobject.put("respDesc", "OK");
				
			}			
		}catch(Exception e){
			e.printStackTrace();	
		}finally{
			if(wb!=null){
				try{ 
					wb.close();
				}catch (Exception e2) {
					e2.printStackTrace();
				}
			}
			
			
		}
		return jsobject;
	}
	
	
	private String getCellValue(Cell cell){
		String cellValue = "";
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC: 
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    cellValue = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue())).toString();
                } else {
                    DataFormatter dataFormatter = new DataFormatter();
                    cellValue = dataFormatter.formatCellValue(cell);
                }
                break;
            case Cell.CELL_TYPE_STRING:
                cellValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_BOOLEAN: 
                cellValue = cell.getBooleanCellValue() + "";
                break;
            case Cell.CELL_TYPE_FORMULA: 
                cellValue = cell.getCellFormula() + "";
                break;
            case Cell.CELL_TYPE_BLANK: 
                cellValue = "";
                break;
            default:
                cellValue = cell.toString();
        }
        return cellValue;
	}
	
	@Override
	public JSONObject tablelist(JSONObject jsonStr,Page page){
		JSONObject jsobject = new JSONObject();
		jsobject.put("respCode", "-1");
		jsobject.put("respDesc", "获取配置异常");
		try{
			String sql = "SELECT table_id, table_name, table_desc, template_url FROM import_table  WHERE STATUS = 'A' ";
			jsobject = baseDAO.findBySqlToJosn(sql, page);			
		}catch(Exception e){
			e.printStackTrace();
		}
		return jsobject;		
	}
	
	@Override
	public JSONObject deltable(String tableId){
		JSONObject jsobject = new JSONObject();
		jsobject.put("respCode", "-1");
		jsobject.put("respDesc", "删除失败");
		try{
			Map<String,ArrayList<JavaModel>> map = new HashMap<>();
			String sql = "UPDATE import_table SET STATUS = 'P' WHERE table_id = ?";
			ArrayList<JavaModel> params = new ArrayList<>();
			params.add(new JavaModel(Integer.valueOf(tableId)));
			map.put(sql, params);
			sql = "UPDATE import_table_detail SET STATUS='P' WHERE table_id = ?";
			map.put(sql, params);
			if(baseDAO.excuteBatchSql(map)){
				jsobject.put("respCode", "0");
				jsobject.put("respDesc", "删除成功");				
			}			
		}catch(Exception e){
			e.printStackTrace();
		}
		return jsobject;
	}
	
	
	@Override
	public JSONObject savetable(JSONObject jsdata){
		JSONObject jsobject = new JSONObject();
		jsobject.put("respCode", "-1");
		jsobject.put("respDesc", "保存失败");
		String tableId = jsdata.getString("tableId")==null?"":jsdata.getString("tableId");
		String tableName = jsdata.getString("tableName");
		String tableDesc = jsdata.getString("tableDesc");
		String tempUrl = jsdata.getString("tempUrl");
		try{
			JSONArray fields = jsdata.getJSONArray("fields");
			ArrayList<JavaModel> params = new ArrayList<>();
			String sql = "";
			LinkedHashMap<JavaSql,ArrayList<JavaModel>> map = new LinkedHashMap<>();
			if(StringUtils.isEmpty(tableId)){
				sql = "INSERT INTO import_table SET table_name = ?, table_desc = ?, template_url=?, status='A' ";
				params.add(new JavaModel(tableName));
				params.add(new JavaModel(tableDesc));
				params.add(new JavaModel(tempUrl));
				baseDAO.excuteSql(sql, params);
				sql = "SELECT * FROM import_table WHERE status='A' AND template_url='"+tempUrl+"' ";
				JSONObject table = baseDAO.findBySqlToJosn(sql).getJSONArray("list").getJSONObject(0);
				tableId = table.getInteger("table_id")+"";
			}else{
				sql = "UPDATE import_table SET table_name = ?, table_desc = ?, template_url=? WHERE table_id = ? ";
				params.add(new JavaModel(tableName));
				params.add(new JavaModel(tableDesc));
				params.add(new JavaModel(tempUrl));
				params.add(new JavaModel(Integer.valueOf(tableId)));
				JavaSql jsql = new JavaSql(0, sql);
				map.put(jsql, params);
				sql = "DELETE FROM import_table_detail WHERE table_id = ? AND status = 'A' ";
				jsql = new JavaSql(1,sql);
				params = new ArrayList<>();
				params.add(new JavaModel(Integer.valueOf(tableId)));
				map.put(jsql, params);
			}
			sql = "INSERT INTO import_table_detail SET table_id=?, filed_name=?, filed_desc=?, status='A', data_type=?, real_filed_name=? ";
			for(int i=0;i<fields.size();i++){
				JSONObject field = fields.getJSONObject(i);
				JavaSql jsql = new JavaSql(i, sql);
				params = new ArrayList<>();
				params.add(new JavaModel(Integer.valueOf(tableId)));
				params.add(new JavaModel(field.getString("filed_name")));
				params.add(new JavaModel(field.getString("filed_desc")));
				params.add(new JavaModel(field.getString("data_type")));
				params.add(new JavaModel(field.getString("real_filed_name")));
				map.put(jsql, params);
			}
			baseDAO.excuteBatchOrderedSqlEx(map);
			jsobject.put("respCode", "0");
			jsobject.put("respDesc", "保存成功");			
			
		}catch(Exception e){
			e.printStackTrace();
			
		}
		return jsobject;
	}
	
	
	
}
